check_postgres_bloat

--_85e18b78-35f1-4e3f-9142-d179d668e352_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi=2C

=20

I'm running a the check_postgres.pl --action=3Dbloat on a database and find=
ing that there is wasted space.

=20

I'm using 95% for the crtical %. If I use 110% I get the same things=2C bu=
t 115% shows everything is OK.

=20

check_postgres_bloat -H host -p port -db thing -t thing1 -c 95%

check_postgres.pl version 2.14.3

postgres version 8.3.9

=20

=20

POSTGRES_BLOAT CRITICAL: service=3Dthing1 (db thing) table public.thing1 ro=
ws:25537152 pages:212311 shouldbe:187545 (1.1X) wasted size:202883072 (193 =
MB) * (db thing) table public.thing2 rows:10109173 pages:101832 shouldbe:94=
016 (1.1X) wasted size:64028672 (61 MB) * (db thing) table thing3 rows:1184=
946 pages:28701 shouldbe:21434 (1.3X) wasted size:59531264 (56 MB) *=20
=20
=20
I have run a reindexdb on the table=2C full vacuum (fze)=2C still I can't g=
et the wasted size to go down on these=2C so I continue to get critical ale=
rts that my tables are bloated.
=20
check_postgres checks for both index and table bloat. It looks like my ind=
exes are ok=2C this is just picking up on table bloat. I'm not sure what I=
can do to reclaim the wasted space other than vacuum full & analyze. Mayb=
e a pgdump will do it.
=20
Thanks for any insight=2C
~DjK
=20
=20
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=

--_85e18b78-35f1-4e3f-9142-d179d668e352_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






Hi=2C

 =3B

I'm running a the check_postgres.pl --action=3Dbloat on a database and find=
ing that there is wasted space.

 =3B

I'm using 95% for the crtical %. =3B If I use 110% I get the same thing=
s=2C but 115% shows everything is OK.

 =3B

check_postgres_bloat -H host -p port -db thing -t thing1 -c 95%

check_postgres.pl version 2.14.3

postgres version 8.3.9

 =3B

 =3B

=3DConsolas>POSTGRES_BLOAT CRITICAL: service=3Dthing1 (db thing) table publ=
ic.thing1 rows:25537152 pages:212311 shouldbe:187545 (1.1X) wasted size:202=
883072 (193 MB) * (db thing) table public.thing2 rows:10109173 pages:101832=
shouldbe:94016 (1.1X) wasted size:64028672 (61 MB) * (db thing) table =
=3Bthing3 rows:1184946 pages:28701 shouldbe:21434 (1.3X) wasted size:595312=
64 (56 MB) * =3B

=3DConsolas> =3B

 =3B

=3DConsolas>I =3Bhave run a reindexdb on the table=2C =3Bfull vacuu=
m (fze)=2C still I can't get the wasted size to go down on these=2C so I co=
ntinue to get critical alerts that my tables are bloated.

=3DConsolas> =3B

=3DConsolas>check_postgres checks for both index and table bloat. =3B I=
t looks like my indexes are ok=2C this is just picking up on table bloat.&n=
bsp=3B I'm not sure what I can do to reclaim the wasted space other than va=
cuum full &=3B =3Banalyze. =3B Maybe a pgdump will do it.=

=3DConsolas> =3B

=3DConsolas>Thanks for any insight=2C

=3DConsolas>~DjK

 =3B

 =3B

Re: check_postgres_bloat

On Fri, Jun 11, 2010 at 10:50:20AM -0400, dx k9 wrote:
>
> Hi,
>
>
>
> I'm running a the check_postgres.pl --action=bloat on a database and finding that there is wasted space.
>
>
>
> I'm using 95% for the crtical %. If I use 110% I get the same things, but 115% shows everything is OK.
>
>
>
> check_postgres_bloat -H host -p port -db thing -t thing1 -c 95%
>
> check_postgres.pl version 2.14.3
>
> postgres version 8.3.9
>
>
>
>
>
> POSTGRES_BLOAT CRITICAL: service=thing1 (db thing) table public.thing1 rows:25537152 pages:212311 shouldbe:187545 (1.1X) wasted size:202883072 (193 MB) * (db thing) table public.thing2 rows:10109173 pages:101832 shouldbe:94016 (1.1X) wasted size:64028672 (61 MB) * (db thing) table thing3 rows:1184946 pages:28701 shouldbe:21434 (1.3X) wasted size:59531264 (56 MB) *
>
>
> I have run a reindexdb on the table, full vacuum (fze), still I can't get the wasted size to go down on these, so I continue to get critical alerts that my tables are bloated.
>
> check_postgres checks for both index and table bloat. It looks like my indexes are ok, this is just picking up on table bloat. I'm not sure what I can do to reclaim the wasted space other than vacuum full & analyze. Maybe a pgdump will do it.
>
> Thanks for any insight,
> ~DjK
>
Because of the way PostgreSQL performs updates, your database will
perform better with a little bit of extra space over the amount
needed for your data. Unless you have a read-only database, just
use normal vacuum via autovacuum and forget about vacuum full and
trying to get "wasted" space to zero.

Regards,
Ken

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: check_postgres_bloat

This is a multi-part message in MIME format.
--------------020009080707070807090100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

dx k9 wrote:
>
> check_postgres checks for both index and table bloat. It looks like
> my indexes are ok, this is just picking up on table bloat. I'm not
> sure what I can do to reclaim the wasted space other than vacuum full
> & analyze. Maybe a pgdump will do it.
>

CLUSTER will rebuild a new copy of the table without any table bloat,
and it's much faster than VACUUM FULL. See
http://wiki.postgresql.org/wiki/VACUUM_FULL

Note that the bloat estimate from check_postgres is extremely rough and
it's quite possible to get misleading results from it. I wouldn't do
anything just based on an initial report from it that a table is bloated
other than move the thresholds up until it stops complaining. The idea
is that once calibrated usefully to what is normal bloat levels for your
app by its measurement technique, you then monitor for excess bloat
outside of historical norms. You should not assume the number itself is
really accurate, and you should do a manual VACUUM VERBOSE against the
table to see if it's right or not before taking drastic action (like
VACUUM FULL or CLUSTER). You might also use pg_freespacemap instead to
compute more accurate bloat numbers, but most people consider that too
much work relative to the improvement you get over the simpler
check_postgres estimate.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us


--------------020009080707070807090100
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit







dx k9 wrote:

type="cite">

face="Consolas" size="3">check_postgres checks for both index and
table bloat.  It looks like my indexes are ok, this is just picking up
on table bloat.  I'm not sure what I can do to reclaim the wasted space
other than vacuum full & analyze.  Maybe a pgdump will do it.

-- 

Greg Smith  2ndQuadrant US  Baltimore, MD

PostgreSQL Training, Services and Support